﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Web.Security;
using Common.Logging;

namespace DbExtendedProvider
{
	public abstract class DbCommander<TConn, TComm, TParam> : IDbCommander
		where TConn : class, IDbConnection, new()
		where TComm : class, IDbCommand, new()
		where TParam : class, IDbDataParameter, new()
	{
		#region setup

		private static readonly ILog Logger = LogManager.GetCurrentClassLogger();
		protected Dictionary<string, string> Resources = new Dictionary<string, string>();
		public abstract bool TransactionsSupported { get; }

		protected virtual string ParameterPrefix
		{
			get { return "@"; }
		}

		public abstract string DDLResourcePath { get; }
		public abstract string DMLResourcePath { get; }
		public virtual string ConnectionStringName { get; set; }
		public virtual int LockoutTime { get; set; }

		public virtual void Initialize(string applicationName,
									   string providerName,
									   int lockoutTime)
		{
			ApplicationName = applicationName;
			ProviderName = providerName;
		}

		public virtual string ApplicationName { get; set; }
		public virtual string ProviderName { get; set; }

		protected virtual TConn OpenConnection()
		{
			try
			{
				var conn = new TConn
					{
						ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString
					};
				conn.Open();
				return conn;
			}
			catch (Exception ex)
			{
				Logger.Error(e => e("OpenConnection(string): {0}", ex));
				throw;
			}
		}

		protected virtual TComm GetCommand(TConn connection,
										   IDbTransaction transaction,
										   bool addApplicationName = false)
		{
			try
			{
				if (connection == null) throw new ArgumentNullException("connection");
				var comm = TransactionsSupported
							   ? new TComm { Connection = connection, Transaction = transaction }
							   : new TComm { Connection = connection };
				if (addApplicationName) comm.Parameters.Add(CreateParameter("application_name", ApplicationName, DbType.String));
				comm.CommandType = CommandType.Text;
				return comm;
			}
			catch (Exception ex)
			{
				Logger.Error(e => e("GetCommand({0}): {1}", typeof(TConn).Name, ex));
				throw;
			}
		}

		protected virtual TParam CreateParameter(string name,
												 object value,
												 DbType type)
		{
			var param = new TParam
				{
					DbType = type,
					ParameterName = string.Format("{0}{1}", ParameterPrefix, name),
					Value = value ?? DBNull.Value
				};
			return param;
		}

		protected virtual IDbTransaction OpenTransaction(TConn connection,
														 IsolationLevel isolation = IsolationLevel.ReadUncommitted)
		{
			return TransactionsSupported ? connection.BeginTransaction(isolation) : new DummyTransaction(connection, isolation);
		}

		protected virtual string GetSql(string scriptName,
										bool ddl = false)
		{
			try
			{
				if (!ddl && Resources.ContainsKey(scriptName)) return Resources[scriptName];
				var path = string.Format("{0}.{1}", ddl ? DDLResourcePath : DMLResourcePath, scriptName);
				using (var resource = GetType().Assembly.GetManifestResourceStream(path))
					if (resource != null)
						using (var reader = new StreamReader(resource))
						{
							var script = reader.ReadToEnd();
							//only cache DML scripts.
							if (!ddl) Resources[scriptName] = script;
							return script;
						}
					else
					{
						throw new NullReferenceException("Could not find the resource file.");
					}
			}
			catch (Exception ex)
			{
				Logger.Error(e => e("Could not retrieve script from manifest file {0}: {2} (DDL= {1})", scriptName, ddl, ex.Message));
				throw;
			}
		}

		protected virtual IList<MembershipUser> ToMembershipUsers(IDataReader reader)
		{
			if (reader == null) throw new ArgumentNullException("reader");
			if (reader.IsClosed) throw new InvalidOperationException("This datareader cannot be read from.  It is closed.");

			var users = new List<MembershipUser>();

			var userIdColumn = reader.GetOrdinal("user_id");
			var userNameColumn = reader.GetOrdinal("user_name");
			var lastActivityColumn = reader.GetOrdinal("last_activity_timestamp");
			var createdColumn = reader.GetOrdinal("created_timestamp");
			var emailColumm = reader.GetOrdinal("email");
			var approvedColumn = reader.GetOrdinal("enabled");
			var lastLockoutColumn = reader.GetOrdinal("last_locked_out_timestamp");
			var lastLoginColumn = reader.GetOrdinal("last_login_timestamp");
			var lastPasswordChangedColumn = reader.GetOrdinal("last_password_changed_timestamp");
			var passwordQuestionColumn = reader.GetOrdinal("security_question");
			var commentsColumn = reader.GetOrdinal("comments");

			while (reader.Read())
			{
				var user = new MembershipUser(
					ProviderName,
					reader.IsDBNull(userNameColumn) ? string.Empty : reader.GetString(userNameColumn),
					reader.IsDBNull(userIdColumn) ? 0 : reader.GetInt32(userIdColumn),
					reader.IsDBNull(emailColumm) ? string.Empty : reader.GetString(emailColumm),
					reader.IsDBNull(passwordQuestionColumn) ? string.Empty : reader.GetString(passwordQuestionColumn),
					reader.IsDBNull(commentsColumn) ? string.Empty : reader.GetString(commentsColumn),
					!reader.IsDBNull(approvedColumn) && reader.GetBoolean(approvedColumn),
					!reader.IsDBNull(lastLockoutColumn) && LockoutInEffect(reader.GetDateTime(lastLockoutColumn)),
					reader.IsDBNull(createdColumn) ? DateTime.MinValue : reader.GetDateTime(createdColumn),
					reader.IsDBNull(lastLoginColumn) ? DateTime.MinValue : reader.GetDateTime(lastLoginColumn),
					reader.IsDBNull(lastActivityColumn) ? DateTime.MinValue : reader.GetDateTime(lastActivityColumn),
					reader.IsDBNull(lastPasswordChangedColumn) ? DateTime.MinValue : reader.GetDateTime(lastPasswordChangedColumn),
					reader.IsDBNull(lastLockoutColumn) ? DateTime.MinValue : reader.GetDateTime(lastLockoutColumn)
					);

				users.Add(user);
			}

			return users;
		}

		protected virtual bool LockoutInEffect(DateTime dateTime)
		{
			//there is no lockout if the dateTime is empty
			if (dateTime == DateTime.MinValue) return false;

			//there is no auto unlock if the LockoutTime is zero.
			if (LockoutTime == 0) return true;

			return dateTime.AddMinutes(LockoutTime) > DateTime.Now;
		}

		#endregion

		public virtual bool UserExistsByUserName(string userName)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("user_exists_by_username.txt");
				comm.Parameters.Add(CreateParameter("user_name", userName, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					return reader != null && reader.Read();
				}
			}
		}

		public virtual bool UserExistsByEmail(string email)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("user_exists_by_email.txt");
				comm.Parameters.Add(CreateParameter("email", email, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					return reader != null && reader.Read();
				}
			}
		}

		public virtual bool UserExistsByUserId(Guid id)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("user_exists_by_user_id.txt");
				comm.Parameters.Add(CreateParameter("@user_id", id, DbType.Guid));
				using (var reader = comm.ExecuteReader())
				{
					return reader != null && reader.Read();
				}
			}
		}

		public virtual MembershipUser CreateStandardUser(
			string username,
			string salt,
			byte[] password,
			string comments,
			string email,
			out MembershipCreateStatus status,
			Guid id,
			string securityQuestion = null,
			string securityAnswerSalt = null,
			byte[] securityAnswer = null,
			bool enabled = true)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("create_user.txt");
				comm.Parameters.Add(CreateParameter("user_id", id, DbType.Guid));

				//if the salt is empty, this is a reversible encryption instead of a salted hash.
				if (string.IsNullOrEmpty(salt))
				{
					comm.Parameters.Add(CreateParameter("salt", DBNull.Value, DbType.String));
					comm.Parameters.Add(CreateParameter("salted_hash", DBNull.Value, DbType.Binary));
					comm.Parameters.Add(CreateParameter("reversible_password", password, DbType.Binary));
				}
				else
				{
					comm.Parameters.Add(CreateParameter("salt", salt, DbType.String));
					comm.Parameters.Add(CreateParameter("salted_hash", password, DbType.Binary));
					comm.Parameters.Add(CreateParameter("reversible_password", DBNull.Value, DbType.Binary));
				}
				comm.Parameters.Add(CreateParameter("email", email, DbType.String));
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				comm.Parameters.Add(CreateParameter("comments", comments, DbType.String));
				comm.Parameters.Add(CreateParameter("enabled", enabled, DbType.Boolean));
				comm.Parameters.Add(CreateParameter("comments", comments, DbType.String));
				comm.Parameters.Add(CreateParameter("security_question", securityQuestion, DbType.String));
				comm.Parameters.Add(CreateParameter("security_answer_hash", securityAnswer, DbType.Binary));
				comm.Parameters.Add(CreateParameter("security_answer_salt", securityAnswerSalt, DbType.String));

				using (var reader = comm.ExecuteReader())
				{
					var users = ToMembershipUsers(reader);
					if (users == null || users.Count != 1)
					{
						status = MembershipCreateStatus.ProviderError;
						return null;
					}
					status = MembershipCreateStatus.Success;
					trans.Commit();
					return users.First();
				}
			}
		}

		public virtual bool ConnectionIsGood()
		{
			try
			{
				using (var conn = OpenConnection())
				using (var trans = OpenTransaction(conn))
				using (var comm = GetCommand(conn, trans))
				{
					comm.CommandText = "select 1;";
					var returnVal = (int)comm.ExecuteScalar();
					return returnVal == 1;
				}
			}
			catch (Exception ex)
			{
				Logger.Error("ConnectionIsGood", ex);
				return false;
			}
		}

		public virtual byte[] GetEncryptedPassword(string username)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("get_encrypted_password.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					if (reader == null || !reader.Read())
					{
						throw new UserNotFoundException(username);
					}
					var column = reader.GetOrdinal("reversible_password");
					var val = reader.GetValue(column) as byte[];
					return val;
				}
			}
		}

		public virtual HashGrouping GetHashedPassword(string username)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("get_hashed_password.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					if (reader == null || !reader.Read())
					{
						throw new UserNotFoundException(username);
					}
					return GetHashGrouping(reader);
				}
			}
		}

		public virtual HashGrouping GetHashedAnswer(string username)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("get_hashed_answer.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					if (reader == null || !reader.Read())
					{
						throw new UserNotFoundException(username);
					}
					return GetHashGrouping(reader);
				}
			}
		}

		public virtual void UnlockUser(string username)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("unlock_user.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				comm.ExecuteNonQuery();
				trans.Commit();
			}
		}

		public virtual void UpdateQuestionAndAnswer(string username,
													string question,
													string answerSalt,
													byte[] answerHash)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("update_password_question_and_answer.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				comm.Parameters.Add(CreateParameter("security_question", question, DbType.String));
				comm.Parameters.Add(CreateParameter("security_answer_salt", answerSalt, DbType.String));
				comm.Parameters.Add(CreateParameter("security_answer_hash", answerHash, DbType.Binary));
				comm.ExecuteNonQuery();
				trans.Commit();
			}
		}

		public virtual void ChangeEncryptedPassword(string username,
											byte[] password)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("change_encrypted_password.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				comm.Parameters.Add(CreateParameter("encrypted_password", password, DbType.Binary));
				comm.ExecuteNonQuery();
				trans.Commit();
			}
		}

		protected virtual HashGrouping GetHashGrouping(IDataReader reader)
		{
			var saltColumn = reader.GetOrdinal("salt");
			var hashColumn = reader.GetOrdinal("hash");
			return
				new HashGrouping
					{
						Salt = reader.GetValue(saltColumn) as string,
						Hash = reader.GetValue(hashColumn) as byte[]
					};
		}


		public virtual void ChangeHashedPassword(string username, string salt, byte[] hash)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("change_encrypted_password.txt");
				comm.Parameters.Add(CreateParameter("user_name", username, DbType.String));
				comm.Parameters.Add(CreateParameter("salt", salt, DbType.String));
				comm.Parameters.Add(CreateParameter("hash", hash, DbType.Binary));
				comm.ExecuteNonQuery();
				trans.Commit();
			}
		}

		public virtual MembershipUser UpdateMembershipUser(MembershipUser user)
		{
			if (user == null) throw new ArgumentNullException("user");
			if (user.ProviderUserKey == null) throw new NullReferenceException("user.ProviderUserKey");
			var userId = (Guid)user.ProviderUserKey;
			var existing = GetMembershipUser(userId);
			if (user.UserName.Equals(existing.UserName, StringComparison.InvariantCultureIgnoreCase))
			{
				//check to make sure the username is not in use
				if (UserExistsByUserName(user.UserName))
				{
					throw new DuplicateUserNameException(user.UserName);
				}
			}

			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans))
			{
				comm.CommandText = GetSql("update_user.txt");
				comm.Parameters.Add(CreateParameter("user_id", userId, DbType.Guid));
				comm.Parameters.Add(CreateParameter("user_name", user.UserName, DbType.String));
				comm.Parameters.Add(CreateParameter("comments", user.Comment, DbType.String));
				comm.Parameters.Add(CreateParameter("enabled", user.IsApproved, DbType.Boolean));
				using (var reader = comm.ExecuteReader())
				{
					var users = ToMembershipUsers(reader);
					if (users.Count != 1)
					{
						// dunno what sort of exception to throw here?
						return null;
					}
					trans.Commit();
					return users.First();
				}
			}
		}

		public virtual MembershipUser GetMembershipUser(string userName)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans, true))
			{
				comm.CommandText = GetSql("get_user_by_user_name.txt");
				comm.Parameters.Add(CreateParameter("user_name", userName, DbType.String));
				using (var reader = comm.ExecuteReader())
				{
					var users = ToMembershipUsers(reader);
					if (users.Count != 1)
					{
						// dunno what sort of exception to throw here?
						return null;
					}
					return users.First();
				}
			}
		}

		public virtual MembershipUser GetMembershipUser(Guid userId)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans))
			{
				comm.CommandText = GetSql("get_user_by_id.txt");
				comm.Parameters.Add(CreateParameter("user_id", userId, DbType.Guid));
				using (var reader = comm.ExecuteReader())
				{
					var users = ToMembershipUsers(reader);
					if (users.Count != 1)
					{
						// dunno what sort of exception to throw here?
						return null;
					}
					return users.First();
				}
			}
		}

		public virtual IEnumerable<MembershipUser> GetMembershipUsersByEmail(string email)
		{
			using (var conn = OpenConnection())
			using (var trans = OpenTransaction(conn))
			using (var comm = GetCommand(conn, trans))
			{
				comm.CommandText = GetSql("get_users_by_email.txt");
				comm.Parameters.Add(CreateParameter("email", email, DbType.Guid));
				using (var reader = comm.ExecuteReader())
				{
					var users = ToMembershipUsers(reader);
					return users;
				}
			}
		}

	}
}